﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using DevExpress.Xpo;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace ChuanPlus.DAO
{
    class ReceiptVoucherDAO
    {
        //
        // save ReceiptVoucher to db, update customer as 1 Unit of work
        //
        public static void save(ReceiptVoucher rv)
        {
            // 
            Customer customer = rv.CustomerID;
            double moneyLeft = rv.Amount;
            //select order cua customer theo thu tu ngay thang
            Session.DefaultSession.BeginTransaction();
            try
            {

                List<Order> arrOrder = getUnpaidOrder(customer);
                // tong so tien phai tra cua khach hang                
                if (customer.Credit < moneyLeft)
                    throw new Exception("receipt total greater than customer credit");
                //return arrOrder;
                //tru dan no trong order
                int count = arrOrder.Count();
               
                int i = 0;
                // check tong so tien co du tra cho debt hok
                if (count > 0)  // neu khach hang co hoa don thieu
                {
                    while (moneyLeft > 0 && i < count)
                    {
                        double debt = arrOrder[i].Total - arrOrder[i].PayInAdvance;
                        if (debt > 0)
                        {
                            if (moneyLeft >= debt)
                            {
                                arrOrder[i].PayInAdvance += debt;
                                arrOrder[i].Save();
                                moneyLeft -= debt;
                                // tao 1 payment
                                Payment payment = new Payment(Session.DefaultSession, arrOrder[i], rv, debt);
                                payment.Save();
                            }
                            else
                            {
                                arrOrder[i].PayInAdvance += moneyLeft;
                                arrOrder[i].Save();
                                // tao 1 payment
                                Payment payment = new Payment(Session.DefaultSession, arrOrder[i], rv, moneyLeft);
                                payment.Save();
                                moneyLeft = 0;
                            }
                        }
                        i++;
                    }
                }
                    // neu khach hang chi thieu tien trog customer credit, ko co hoa don no
                else
                {
                   // tam thoi chua xet
                }
                
                rv.Status = "P";
                rv.Save();
                Session.DefaultSession.CommitTransaction();
            }
            catch (Exception ex)
            {
                Session.DefaultSession.RollbackTransaction();
                throw new Exception(ex.Message);
            }

            /// old version
            /// fat sinh nhiu thu fuc tap
            ////Tru nợ cua customer -- field credit
            //Customer cus = rv.CustomerID;
            //cus.Credit -= rv.Amount;
            //cus.Save();
            //// Save ReceiptVoucher
            //rv.Save();
            ///
         
        }
        public static List<ReceiptVoucher> getReceiptVouchers(string status)
        {
            try
            {
                XPQuery<ReceiptVoucher> receiptVoucher = new XPQuery<ReceiptVoucher>(Session.DefaultSession);
                var list = from rv in receiptVoucher
                           where rv.Status == status
                           select rv;
                return new List<ReceiptVoucher>(list.AsEnumerable<ReceiptVoucher>());
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        public static List<Order> getUnpaidOrder(Customer customer)
        {
            XPQuery<Order> orders = new XPQuery<Order>(Session.DefaultSession);
            var listOrder = from o in orders
                            where (o.CustomerID == customer && o.PayInAdvance < o.Total && o.Status == "P")
                            orderby o.OrderDate ascending
                            select o;
            List<Order> arrOrder = new List<Order>(listOrder.AsEnumerable<Order>());
            return arrOrder;
        }
        public static List<ReceiptVoucher> getReceiptVoucher(DateTime FromDate, DateTime ToDate) //ok
        {
            try
            {
            XPQuery<ReceiptVoucher> receipt = new XPQuery<ReceiptVoucher>(Session.DefaultSession);
            var list = from r in receipt
                       where ((r.ReceiptDate >= FromDate) && (r.ReceiptDate <= ToDate))
                       orderby r.ReceiptDate descending
                       select r;
            List<ReceiptVoucher> arr = new List<ReceiptVoucher>(list.AsEnumerable<ReceiptVoucher>());
            
                return arr;
            }
            catch (Exception e) { throw e; }
        }
        
        //
        // create monthly collection report 
        //    
        public static DataTable GetCollectionReport(DateTime fromDate, DateTime toDate)
        {
            try
            {
                MySqlParameter[] parameters = new MySqlParameter[2];
                parameters[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
                parameters[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
                parameters[0].Value = fromDate;
                parameters[1].Value = toDate;
                return DataProvider.execute("collectionReport", parameters);               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return null;
        }
        //Nói chung là hỏi lại !!!
        // create monthly debtor report 
        // Khach hang` nợ or supplier nợ ???
        // Lay danh sách nhung nguoi nợ trong tháng (dựa vào đơn đặt hàng) or thông tin khách hàng nào đó nợ ???
        public static DataTable GetDebtorReport(int customer, DateTime fromDate, DateTime toDate)
        {
            MySqlParameter[] parameters = new MySqlParameter[3];
            parameters[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
            parameters[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
            parameters[2] = new MySqlParameter("?customerID", MySqlDbType.Int32);

            parameters[0].Value = fromDate;
            parameters[1].Value = toDate;
            parameters[2].Value = customer;
            return DataProvider.execute("spDebtorReport", parameters);
        }
        public static double GetOpeningDebtorBalance(int customer, DateTime fromDate)
        {
            MySqlParameter[] parameters = new MySqlParameter[2];
            parameters[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
            parameters[1] = new MySqlParameter("?customerID", MySqlDbType.Int32);

            parameters[0].Value = fromDate;
            parameters[1].Value = customer;

            string sql = "spGetOpeningDebtorBalance";
            AppSettingsReader config = new AppSettingsReader();
            sql = ((string)(config.GetValue("Database", typeof(string)))) + "." + sql;
            MySqlConnection conn = new MySqlConnection(ChuanPlus.Properties.Settings.Default.ChuanPlusConnectionString);
            MySqlCommand command = new MySqlCommand(sql, conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddRange(parameters);
            double balance = 0;
            try
            {
                conn.Open();
                Object objBalance = command.ExecuteScalar();
                if(objBalance != null)
                    if (!Double.TryParse(objBalance.ToString(),out balance)) balance = 0;                
                conn.Close();
            }
            catch (Exception ex)
            {
                if (conn != null) conn.Close();
                throw ex;
            }
            return balance;
        }
    }
}
